﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.GERP.GHRMS.IDAL;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using System.Data;
//using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace KPIS.GERP.GHRMS.DAL
{
    public class HRDEvaluationDAL : IHRDEvaluationDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        SqlConnection conn = new SqlConnection(SystemInfo._ConnectionString);

        public DataTable getPosSeq(int intEmpSequence)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@EmpSequence", intEmpSequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT a.emp_seq, a.first_name, a.last_name, a.pos_seq, b.exec_pos_seq  "
                         + " FROM hrm_ps_employees a "
                         + " INNER JOIN hrm_mp_positions b "
                         + " ON a.pos_seq = b.pos_seq "
                         + " WHERE a.emp_seq = @EmpSequence"
                         + " AND a.record_status not like @DeleteRecord";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadGoalObjective(int intEmpSequence)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@EmpSequence", intEmpSequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                // validate column
                dbManager.ExecuteNonQuery(CommandType.Text, @"
IF COL_LENGTH('hrm_perf_agreement','action_plan_score') IS NULL
 BEGIN
	ALTER TABLE hrm_perf_agreement ADD action_plan_score INT;
 END");
                // validate column
                dbManager.ExecuteNonQuery(CommandType.Text, @"
IF COL_LENGTH('hrm_perf_agreement','action_plan_score_by_supervisor') IS NULL
 BEGIN
	ALTER TABLE hrm_perf_agreement ADD action_plan_score_by_supervisor INT;
 END");



                string strSQL = " SELECT goal_objective, action_plan, action_plan_score, action_plan_score_by_supervisor"
                         + " FROM hrm_perf_agreement "
                         + " WHERE emp_seq = @EmpSequence"
                         + " AND record_status not like @DeleteRecord";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadGridDevelopmentNeed(int intEmpSequence)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@EmpSequence", intEmpSequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT * FROM hrm_perf_agreement "
                         + " INNER JOIN hrm_perf_agreement_detail "
                         + " ON hrm_perf_agreement_detail.perf_agreement_seq = hrm_perf_agreement.perf_agreement_seq "
                         + " INNER JOIN hrm_perf_competency_type "
                         + " ON hrm_perf_competency_type.competency_type_seq = hrm_perf_agreement_detail.competency_type_seq "
                         + " INNER JOIN hrm_perf_proficiency "
                         + " ON hrm_perf_proficiency.proficiency_seq = hrm_perf_agreement_detail.proficiency_seq "
                         + " WHERE hrm_perf_agreement.emp_seq = @EmpSequence"
                         + " AND hrm_perf_agreement.record_status not like @DeleteRecord";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadGridKPA(int intPosSequence)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@intPosSequence", intPosSequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT hrm_perf_key_performance_area.kpa_seq, hrm_mas_key_area.key_area_seq,hrm_mas_key_area.key_area_name,hrm_perf_key_performance_area.description, "
                         + " hrm_perf_key_performance_area.evaluation_list_seq, hrm_perf_key_performance_area.weight "
                         + " ,(SELECT MAX(score_value) FROM hrd_evaluation_score_format WHERE evaluation_list_seq = hrm_perf_key_performance_area.evaluation_list_seq) full_score"
                         + " ,hrd_mas_evaluation_list.isdetailweight "
                         + " ,0 weight "
                         + " FROM hrm_perf_key_performance_area "
                         + " INNER JOIN hrm_mas_key_area "
                         + " ON hrm_mas_key_area.key_area_seq = hrm_perf_key_performance_area.key_area_seq "
                         + " INNER JOIN hrd_mas_evaluation_list "
		                 + " ON hrd_mas_evaluation_list.evaluation_list_seq = hrm_perf_key_performance_area.evaluation_list_seq "
                         + " WHERE hrm_perf_key_performance_area.pos_seq = @intPosSequence"
                         + " AND hrm_perf_key_performance_area.record_status not like @DeleteRecord";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];               
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadGridStrategyProject(int intRoundYear, DateTime dateRoundStartDate, DateTime dateRoundEndDate, int intEmpSequence)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@intRoundYear", intRoundYear);
                dbManager.AddParameters(1, "@dateRoundStartDate", dateRoundStartDate);
                dbManager.AddParameters(2, "@dateRoundEndDate", dateRoundEndDate);
                dbManager.AddParameters(3, "@EmpSequence", intEmpSequence);
                dbManager.AddParameters(4, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT * "
                         + " ,(SELECT MAX(score_value) FROM hrd_evaluation_score_format "
                         + " WHERE evaluation_list_seq =bgm_project_job_assignment.evaluation_list_seq) full_score "
                         + " FROM bgm_project_job_assignment "
                         + " INNER JOIN bgm_project_job_assignee "
                         + " ON bgm_project_job_assignee.bgm_proj_job_assign_seq = bgm_project_job_assignment.bgm_proj_job_assign_seq "
                         + " INNER JOIN bgm_project "
                         + " ON bgm_project.bgm_proj_seq = bgm_project_job_assignment.bgm_proj_seq "
                         + " INNER JOIN hrd_mas_evaluation_list "
                         + " ON hrd_mas_evaluation_list.evaluation_list_seq = bgm_project_job_assignment.evaluation_list_seq "
                         + " WHERE bgm_project.bgm_year = @intRoundYear "
                         + " AND bgm_project_job_assignment.job_status = 2 "
                         + " AND bgm_project_job_assignment.job_start_date >= @dateRoundStartDate "
                         + " AND bgm_project_job_assignment.job_end_date <= @dateRoundEndDate "
                         + " AND bgm_project_job_assignee.emp_seq = @EmpSequence "
                         + " AND bgm_project_job_assignment.record_status not like @DeleteRecord ";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadGridCoreCompetency(int intPosSequence)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@intPosSequence", intPosSequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT * "
                         + " ,(SELECT MAX(score_value) FROM hrd_evaluation_score_format "
                         + " WHERE evaluation_list_seq =hrm_perf_position_core_competency.evaluation_list_seq) full_score "
                         + " FROM hrm_perf_position_core_competency  "
                         + " INNER JOIN hrm_perf_core_competency  "
                         + " ON hrm_perf_core_competency.core_competency_seq =  "
                         + " hrm_perf_position_core_competency.core_competency_seq  "
                         + "  INNER JOIN hrd_mas_evaluation_list "
                         + " ON hrd_mas_evaluation_list.evaluation_list_seq = "
                         + " hrm_perf_position_core_competency.evaluation_list_seq "
                         + " INNER JOIN hrm_perf_competency_level "
                         + " ON hrm_perf_competency_level.core_competency_seq = "
                         + " hrm_perf_position_core_competency.core_competency_seq "
                         + " AND hrm_perf_competency_level.competency_level_seq = "
                         + " hrm_perf_position_core_competency.competency_level_seq "
                         + " AND hrm_perf_position_core_competency.pos_seq = @intPosSequence "
                         + " AND hrm_perf_position_core_competency.record_status not like @DeleteRecord";

                DataTable dt = new DataTable();               
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadGridIDP(int intPosSequence)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@intPosSequence", intPosSequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT * "
                         + " ,(SELECT MAX(score_value) FROM hrd_evaluation_score_format "
                         + " WHERE evaluation_list_seq =hrm_perf_individual_dev_plan.evaluation_list_seq) full_score "
                         + " ,0 weight "
                         + " FROM hrm_perf_individual_dev_plan "
                         + " INNER JOIN hrm_perf_competency_type  "
                         + " ON hrm_perf_competency_type.competency_type_seq =   "
                         + " hrm_perf_individual_dev_plan.competency_type_seq  "
                         + "  INNER JOIN hrd_mas_evaluation_list "
                         + " ON hrd_mas_evaluation_list.evaluation_list_seq = "
                         + " hrm_perf_individual_dev_plan.evaluation_list_seq "
                         + " INNER JOIN hrm_perf_proficiency ON hrm_perf_proficiency.proficiency_seq = "
                         + " hrm_perf_individual_dev_plan.proficiency_seq  "
                         + " WHERE hrm_perf_individual_dev_plan.pos_seq = @intPosSequence "
                         + " AND hrm_perf_individual_dev_plan.record_status not like @DeleteRecord";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadScoreFormat(int intEvaluationListSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@EvaluationListSeq", intEvaluationListSeq);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT score_value,score_text "
                         + " FROM hrd_evaluation_score_format "
                         + " WHERE hrd_evaluation_score_format.evaluation_list_seq = @EvaluationListSeq "
                         + " AND hrd_evaluation_score_format.record_status not like @DeleteRecord";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadEvaluationList()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT *  "
                         + " FROM hrd_mas_evaluation_list "
                         + " WHERE isactive = 1 "
                         + " AND record_status not like @DeleteRecord ";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadRound(string strRoundYear)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@strRoundYear", strRoundYear);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT * "
                         + " FROM hrd_evaluation_round "
                         + " WHERE hrd_evaluation_round.round_year = @strRoundYear"
                         + " AND hrd_evaluation_round.record_status not like @DeleteRecord ";

                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable DeleteRound(int intRoundSeq, int intUpdatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@intRoundSeq", intRoundSeq);
                dbManager.AddParameters(1, "@intUpdatedBy", intUpdatedBy);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " UPDATE hrd_evaluation_round "
                         + " SET record_status = @DeleteRecord "
                         + " ,updated_by = @intUpdatedBy "
                         + " ,updated_when = GETDATE() " // change getdate() to GETDATE() 
                         + " WHERE evaluation_round_seq = @intRoundSeq";

                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        } 

        public DataTable Round(int intEvalRoundSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@intEvalRoundSeq", intEvalRoundSeq);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT *, "
                         + " CONCAT(round_no,'/',round_year)AS round_and_year "
                         + " FROM hrd_evaluation_round "
                         + " WHERE evaluation_round_seq = @intEvalRoundSeq "
                         + " AND record_status not like @DeleteRecord";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable CheckRoundPermission(int intEvaluationRoundSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@intEvaluationRoundSeq", intEvaluationRoundSeq);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT evaluation_round_seq, round_start_date, round_end_date ,permission_start_date, permission_end_date "
                         + " FROM hrd_evaluation_round "
                         + " WHERE evaluation_round_seq = @intEvaluationRoundSeq "
                         + " AND record_status not like @DeleteRecord ";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable CheckEvaluation(int intEmpSequence, int intEvaluationRoundSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@intEmpSequence", intEmpSequence);
                dbManager.AddParameters(1, "@intEvaluationRoundSeq", intEvaluationRoundSeq);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT *  "
                         + " FROM hrd_evaluation_header hd "
                         + " INNER JOIN hrd_evaluation_detail de "
                         + " ON hd.evaluation_seq = de.evaluation_seq "
                         + " INNER JOIN hrd_mas_evaluation_list el "
                         + " ON hd.evaluation_list_seq = el.evaluation_list_seq "
                         + " WHERE hd.emp_seq = @intEmpSequence "
                         + " AND hd.evaluation_round_seq = @intEvaluationRoundSeq "
                         + " AND hd.record_status not like @DeleteRecord";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable CheckOtherScore(int intEmpSequence, int intEvaluationRoundSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@intEmpSequence", intEmpSequence);
                dbManager.AddParameters(1, "@intEvaluationRoundSeq", intEvaluationRoundSeq);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT * FROM hrd_evaluation_header a "
                       + " INNER JOIN hrd_evaluation_detail b ON a.evaluation_seq = b.evaluation_seq "
                       + " LEFT JOIN hrd_evaluation_other_score c "
                       + " ON b.evaluation_detail_seq = c.evaluation_detail_seq "
                       //+ " AND c.other_seq = (SELECT emp_seq FROM hrm_ps_employees WHERE pos_seq =("
                        //+ " SELECT working_upper_pos_seq FROM hrm_ps_employees e"
                        //+ " INNER JOIN hrm_mp_positions p ON p.pos_seq = e.pos_seq"
                        //+ " WHERE emp_seq = a.emp_seq))"
                       + " INNER JOIN hrd_mas_evaluation_list d "
                       + " ON a.evaluation_list_seq = d.evaluation_list_seq "
                       + " WHERE a.emp_seq= @intEmpSequence "
                       + " AND a.evaluation_round_seq = @intEvaluationRoundSeq "
                       + " AND a.record_status not like @DeleteRecord ";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadData(int intEmpSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@intEmpSeq", intEmpSeq);
                dbManager.AddParameters(1, "@vchrStatus", SystemInfo._DeleteRecordStatus);

                //conn.Open();
                //SqlCommand command = new SqlCommand("hr_sp_evaluation_master_data_validate", conn);
                //command.CommandType = CommandType.StoredProcedure;
                //command.Parameters.AddWithValue("intEmpSeq", intEmpSeq);
                //command.Parameters.AddWithValue("vchrStatus", SystemInfo._DeleteRecordStatus);
                
                DataTable dt = new DataTable();

                string strSQL = "hr_sp_evaluation_master_data_validate @intEmpSeq, @vchrStatus";

                //SqlDataAdapter adapter = new SqlDataAdapter(command);
                //adapter.Fill(dt);

                dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        
        public DataTable LoadResultIDP(int intEmpSeq, int intRoundSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@intEmpSeq", intEmpSeq);
                dbManager.AddParameters(1, "@intRoundSeq", intRoundSeq);
                dbManager.AddParameters(2, "@vchrStatus", SystemInfo._DeleteRecordStatus);
                //conn.Open();
                //SqlCommand command = new SqlCommand("hr_sp_evaluation_result_idp_sel", conn);
                //command.CommandType = CommandType.StoredProcedure;
                //command.Parameters.AddWithValue("intEmpSeq", intEmpSeq);
                //command.Parameters.AddWithValue("intRoundSeq", intRoundSeq);
                ////command.Parameters.AddWithValue("vchrStatus", SystemInfo._DeleteRecordStatus);

                DataTable dt = new DataTable();

                string strSQL = "hr_sp_evaluation_result_idp_sel @intEmpSeq, @intRoundSeq";//, @vchrStatus";

                //SqlDataAdapter adapter = new SqlDataAdapter(command);
                //adapter.Fill(dt);
                dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable GetSubData(int head, int intEmpSeq, int intRoundSeq)
        {
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand("hr_sp_evaluation_result_sel", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("intHeadSeq", head);
                command.Parameters.AddWithValue("intEmpSeq", intEmpSeq);
                command.Parameters.AddWithValue("intRoundSeq", intRoundSeq);
                //command.Parameters.AddWithValue("vchrStatus", SystemInfo._DeleteRecordStatus);

                DataTable dt = new DataTable();

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

        }

        public DataTable GetData()
        {
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand("select * from hrd_evaluation_weight_header WHERE category=1 and isweight=1;", conn);
                command.CommandType = CommandType.Text;

                DataTable dt = new DataTable();

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable LoadDevelopmentMethod()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT dev_method_seq,method_name,description "
                            + " FROM hrd_mas_development_method "
                            + " WHERE isactive = 1 "
                            + " AND record_status not like @DeleteRecord ";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetExpectResult(int intEmpSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@intEmpSeq", intEmpSeq);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " SELECT pos_competency_seq ,evaluation_list_seq,expect_result "
                            + " FROM hrm_perf_position_core_competency a "
                            + " INNER JOIN hrm_ps_employees b ON b.pos_seq = a.pos_seq  "
                            + " WHERE b.emp_seq = @intEmpSeq "
                            + " AND a.record_status not like @DeleteRecord ";

                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }
                
        protected int daUpdate(DataTable dtArg,string strStatement,SqlConnection con,SqlTransaction tran)
        {
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(strStatement, con);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

                if (con.State != ConnectionState.Open)
                { con.Open(); }

                builder.ConflictOption = ConflictOption.OverwriteChanges;

                if (adapter.SelectCommand != null)
                {
                    adapter.SelectCommand.Transaction = tran;
                }

                if (adapter.InsertCommand != null)
                {
                    adapter.InsertCommand.Transaction = tran;
                }

                if (adapter.DeleteCommand != null)
                {
                    adapter.DeleteCommand.Transaction = tran;
                }

                if (adapter.UpdateCommand != null)
                {
                    adapter.UpdateCommand.Transaction = tran;
                }

                adapter.Update(dtArg);
            }
            catch (Exception)
            {
                return -1;
                throw;
            }
            finally
            {
                
            }

            return 0;
        
        }

        public DataTable GetHeaderSchema()
        {
            try
            {
                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrd_evaluation_header where 1=2").Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDetailSchema()
        {
            try
            {
                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrd_evaluation_detail where 1=2").Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public string SaveEvaluationScore(DataSet dsKPA, DataSet dsStrategy, DataSet dsCC, DataSet dsIDP)
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();

            try
            {                
                string strHeader = "select * from hrd_evaluation_header where 1=2";
                string strDetail = "select * from hrd_evaluation_detail where 1=2";

                /// KPA ///
                daUpdate(dsKPA.Tables[0], strHeader, conn, tran);
                SqlCommand cmdKPA = new SqlCommand("SELECT CAST(IDENT_CURRENT('hrd_evaluation_header') AS INT)", conn);
                cmdKPA.Transaction = tran;
                int LastIdKPA = Convert.ToInt32(cmdKPA.ExecuteScalar());              
                
                foreach (DataRow row in dsKPA.Tables[1].Rows)
                {
                    row["evaluation_seq"] = LastIdKPA;
                }
                daUpdate(dsKPA.Tables[1], strDetail, conn, tran);


                /// Strategy ///
                daUpdate(dsStrategy.Tables[0], strHeader, conn, tran);
                SqlCommand cmdStrategy = new SqlCommand("SELECT CAST(IDENT_CURRENT('hrd_evaluation_header') AS INT)", conn);
                cmdStrategy.Transaction = tran;
                int LastIdStrategy = Convert.ToInt32(cmdStrategy.ExecuteScalar());
                
                foreach (DataRow row in dsStrategy.Tables[1].Rows)
                {
                    row["evaluation_seq"] = LastIdStrategy;
                }
                daUpdate(dsStrategy.Tables[1], strDetail, conn, tran);


                /// CoreCompetency ///
                daUpdate(dsCC.Tables[0], strHeader, conn, tran);
                SqlCommand cmdCC = new SqlCommand("SELECT CAST(IDENT_CURRENT('hrd_evaluation_header') AS INT)", conn);
                cmdCC.Transaction = tran;
                int LastIdCC = Convert.ToInt32(cmdCC.ExecuteScalar());
                
                foreach (DataRow row in dsCC.Tables[1].Rows)
                {
                    row["evaluation_seq"] = LastIdCC;
                }
                daUpdate(dsCC.Tables[1], strDetail, conn, tran);


                /// IDP ///
                daUpdate(dsIDP.Tables[0], strHeader, conn, tran);
                SqlCommand cmdIDP = new SqlCommand("SELECT CAST(IDENT_CURRENT('hrd_evaluation_header') AS INT)", conn);
                cmdIDP.Transaction = tran;
                int LastIdIDP = Convert.ToInt32(cmdIDP.ExecuteScalar());
                
                foreach (DataRow row in dsIDP.Tables[1].Rows)
                {
                    row["evaluation_seq"] = LastIdIDP;
                }
                daUpdate(dsIDP.Tables[1], strDetail, conn, tran);

                tran.Commit();
                return "Successfully";
            }
             
            catch (Exception)
            {
                throw;
                tran.Rollback();
                return "Failed";
            }
           
            finally
            {
                conn.Close();
               
            }           
            
        }

        public void UpdateApproved(int intEmpSeq, int intEvaluationRoundSeq, int intCreateBy, bool boolApproved)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@intEmpSeq", intEmpSeq);
                dbManager.AddParameters(1, "@intEvaluationRoundSeq", intEvaluationRoundSeq);
                dbManager.AddParameters(2, "@intCreateBy", intCreateBy);
                dbManager.AddParameters(3, "@boolApproved", boolApproved);
                dbManager.AddParameters(4, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " UPDATE hrd_evaluation_header "
                            + " SET isapproved = @boolApproved "
                            + " ,approved_date = GETDATE() "    // change getdate() to GETDATE()
                            + " ,approved_by = @intCreateBy "
                            + " WHERE emp_seq = @intEmpSeq "
                            + " AND evaluation_round_seq = @intEvaluationRoundSeq "
                            + " AND record_status not like @DeleteRecord ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateRound(int intNo, int intYear, DateTime datRoundStart, DateTime datRoundEnd, DateTime datPermissStart, DateTime datPermissEnd
                                , string strRemark, int intUpdateBy, int intEvaluationRoundSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@intNo", intNo);
                dbManager.AddParameters(1, "@intYear", intYear);
                dbManager.AddParameters(2, "@datRoundStart", datRoundStart);
                dbManager.AddParameters(3, "@datRoundEnd", datRoundEnd);
                dbManager.AddParameters(4, "@datPermissStart", datPermissStart);
                dbManager.AddParameters(5, "@datPermissEnd", datPermissEnd);
                dbManager.AddParameters(6, "@strRemark", strRemark);
                dbManager.AddParameters(7, "@intUpdateBy", intUpdateBy);
                dbManager.AddParameters(8, "@intEvaluationRoundSeq", intEvaluationRoundSeq);
                dbManager.AddParameters(9, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = " UPDATE hrd_evaluation_round "
                            + " SET round_no=@intNo ,round_year=@intYear  "
                            + " ,round_start_date=@datRoundStart , round_end_date=@datRoundEnd "
                            + " ,permission_start_date=@datPermissStart ,permission_end_date=@datPermissEnd "
                            + " ,remark=@strRemark ,updated_by=@intUpdateBy ,updated_when=GETDATE()"    //updated_when=getdate()
                            + " WHERE evaluation_round_seq = @intEvaluationRoundSeq "
                            + " AND record_status not like @DeleteRecord ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public void CreateRound(int intNo, int intYear, DateTime datRoundStart, DateTime datRoundEnd, DateTime datPermissStart, DateTime datPermissEnd
                                , string strRemark, int intCreateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@intNo", intNo);
                dbManager.AddParameters(1, "@intYear", intYear);
                dbManager.AddParameters(2, "@datRoundStart", datRoundStart);
                dbManager.AddParameters(3, "@datRoundEnd", datRoundEnd);
                dbManager.AddParameters(4, "@datPermissStart", datPermissStart);
                dbManager.AddParameters(5, "@datPermissEnd", datPermissEnd);
                dbManager.AddParameters(6, "@strRemark", strRemark);
                dbManager.AddParameters(7, "@intCreateBy", intCreateBy);
                dbManager.AddParameters(8, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                string strSQL = " INSERT INTO hrd_evaluation_round "
                            + " (round_no ,round_year  "
                            + " ,round_start_date, round_end_date "
                            + " ,permission_start_date ,permission_end_date "
                            + " ,remark ,record_status ,created_by ,created_when) "
                            + " VALUES (@intNo, @intYear, @datRoundStart ,@datRoundEnd "
                            + " ,@datPermissStart, @datPermissEnd, @strRemark ,@ActiveRecord ,@intCreateBy, GETDATE()) ";   // change getdate() to GETDATE()

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        

        public DataTable GetHeaderSchema(int intEmpSeq, int intEvaluationRoundSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@intEmpSeq", intEmpSeq);
                dbManager.AddParameters(1, "@intEvaluationRoundSeq", intEvaluationRoundSeq);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string sql = "SELECT * FROM hrd_evaluation_header"
                            + " WHERE emp_seq = @intEmpSeq"
                            + " AND evaluation_round_seq = @intEvaluationRoundSeq"
                            + " AND record_status not like @DeleteRecord";

                DataTable dt = new DataTable();                
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetSchemaOtherScore(int intEmpSeq, int intEvaluationRoundSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@intEmpSeq", intEmpSeq);
                dbManager.AddParameters(1, "@intEvaluationRoundSeq", intEvaluationRoundSeq);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string sql = " SELECT * FROM hrd_evaluation_header h "
                            + " INNER JOIN hrd_evaluation_detail d ON h.evaluation_seq = d.evaluation_seq "
                            + " INNER JOIN hrd_evaluation_other_score o ON o.evaluation_detail_seq = d.evaluation_detail_seq "
                            + " WHERE h.emp_seq = @intEmpSeq "
                            + " AND h.evaluation_round_seq = @intEvaluationRoundSeq "
                            + " AND h.record_status not like @DeleteRecord ";

                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                dbManager.Dispose();
            }
        }        
        
        public string SaveEvaluationOtherScore(DataSet dsKPA, DataSet dsStrategy, DataSet dsCC, DataSet dsIDP, int intEmpSeq, int intEvaluationRoundSeq)
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();

            try
            {
                string strHeader = " SELECT * FROM hrd_evaluation_header "
                                + " WHERE emp_seq = " + intEmpSeq
                                + " AND evaluation_round_seq = " + intEvaluationRoundSeq
                                + " AND record_status not like ' " + SystemInfo._DeleteRecordStatus + "' ";

                //string strDetail = " SELECT o.* FROM hrd_evaluation_header h "
                                //+ " INNER JOIN hrd_evaluation_detail d ON h.evaluation_seq = d.evaluation_seq "
                                //+ " INNER JOIN hrd_evaluation_other_score o ON o.evaluation_detail_seq = d.evaluation_detail_seq "
                                //+ " WHERE h.emp_seq = " + intEmpSeq
                                //+ " AND h.evaluation_round_seq = " + intEvaluationRoundSeq
                                //+ " AND h.record_status not like '" + SystemInfo._DeleteRecordStatus + "' ";

                string strDetail = " SELECT * FROM hrd_evaluation_other_score ";

                /// KPA ///
                daUpdate(dsKPA.Tables[0], strHeader, conn, tran);
                daUpdate(dsKPA.Tables[1], strDetail, conn, tran);


                /// Strategy ///
                daUpdate(dsStrategy.Tables[0], strHeader, conn, tran);
                daUpdate(dsStrategy.Tables[1], strDetail, conn, tran);


                /// CoreCompetency ///
                daUpdate(dsCC.Tables[0], strHeader, conn, tran);
                daUpdate(dsCC.Tables[1], strDetail, conn, tran);


                /// IDP ///
                daUpdate(dsIDP.Tables[0], strHeader, conn, tran);
                daUpdate(dsIDP.Tables[1], strDetail, conn, tran);

                tran.Commit();
                return "Successfully";
            }

            catch (Exception)
            {
                throw;
                tran.Rollback();
                return "Failed";
            }

            finally
            {
                conn.Close();

            }

        }

        
    }
}
